﻿Public Class PROVIDERCLASS
    Private EMPID = Nothing
    Private PROVIDER = Nothing
    Private REGISTERNO = Nothing
    Private COUNCIL = Nothing
    Private CID = Nothing
    Private PRENAME = Nothing
    Private NAME = Nothing
    Private LNAME = Nothing
    Private NNAME = Nothing
    Private NAME_EN = Nothing
    Private SEX = Nothing
    Private CLINIC = Nothing
    Private DCL = Nothing
    Private PROTYPE = Nothing
    Private BDATE = Nothing
    Private STDATE = Nothing
    Private APPDATE = Nothing
    Private LEDATE = Nothing
    Private F_CASH = Nothing
    Private D_UPDATE = Nothing
    Private F_DR = Nothing
    Private F_LOGIN = Nothing
    Private USREQ = Nothing
    Private USMK = Nothing
    Private providerstatus = Nothing

    'Private selectMasprename = "SELECT PRENAME, FTPRENAME, FEPRENAME, STPRENAME, SEPRENAME, SEX, convert(bit, [STATUS]) FROM MASPRENAME"
    Private selectProviderInfo = "SELECT h.`empid` AS 'รหัสพนักงาน', h.`cid` AS 'เลขบัตรประชาชน', h.`registerno` AS 'Register No.', h.`provider` AS 'บัญชีผู้ให้บริการ', h.`prename` AS 'รหัสคำนำหน้า', masprename.`ftprename` AS 'คำนำหน้า', h.`name` AS 'ชื่อ', h.`lname` AS 'นามสกุล', CONCAT_WS(' ', masprename.`ftprename`, h.`name`, h.`lname`) AS 'ชื่อ-นามสกุล', h.`nname` AS 'ชื่อเล่น', h.`name_en` AS 'ชื่อ-นามสกุลอังกฤษ', masprename.`sex` AS 'รหัสเพศ', massex.`sexdesc` AS 'เพศ', h.`clinic` AS 'รหัสห้องตรวจ', mc2.`clinicname` AS 'ห้องตรวจ', h.`dcl` AS 'รหัสคลินิกประจำตัว', mc.`clinicname` AS 'คลินิกประจำตัว', h.`protype` AS 'รหัสประเภทผู้ให้บริการ', masprotype.`proname` AS 'ประเภทผู้ให้บริการ', h.`bdate` AS 'วันเกิด', h.`stdate` AS 'วันเริ่มทำงาน', h.`appdate` AS 'วันที่บรรจุ', h.`ledate` AS 'วันลาออก', h.`f_cash` AS 'การเงิน', h.`f_dr` AS 'แพทย์', h.`f_login` AS 'สถานะ login', h.`usreq` AS 'User Request', h.`d_update` AS 'แก้ไขข้อมูลล่าสุด', h.`usmk` AS 'รหัสผู้แก้ไขข้อมูล', CONCAT_WS(' ', h2.`name`, h2.`lname`) AS 'ผู้แก้ไขข้อมูล', h.`status` AS 'การเปิดใช้งาน' FROM hospemp h LEFT JOIN (SELECT * FROM masprename WHERE `status` = 1 ) AS masprename ON h.prename = masprename.prename LEFT JOIN (SELECT * FROM masprotype WHERE `status` = 1) AS masprotype ON h.protype = masprotype.protype LEFT JOIN (SELECT sex, sexdesc FROM massex) AS massex ON h.sex = massex.sex LEFT JOIN (SELECT `empid`, `name`, `lname` FROM hospemp) AS h2 ON h.usmk = h2.empid LEFT JOIN (SELECT clinic, clinicname FROM masclinic) AS mc ON h.dcl = mc.clinic LEFT JOIN (SELECT clinic, clinicname FROM masclinic) AS mc2 ON h.clinic = mc2.clinic "
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection
    Property F_LOGIN_ As Boolean
        Get
            Return F_LOGIN
        End Get
        Set(value As Boolean)
            If value = True Then
                F_LOGIN = 1
            Else
                F_LOGIN = 0
            End If
        End Set
    End Property
    Property F_CASH_ As Boolean
        Get
            Return F_CASH
        End Get
        Set(value As Boolean)
            If value = True Then
                F_CASH = 1
            Else
                F_CASH = 0
            End If
        End Set
    End Property
    Property F_DR_ As Boolean
        Get
            Return F_DR
        End Get
        Set(value As Boolean)
            If value = True Then
                F_DR = 1
            Else
                F_DR = 0
            End If
        End Set
    End Property
    Property D_UPDATE_ As DateTime
        Get
            Return D_UPDATE
        End Get
        Set(value As DateTime)
            D_UPDATE = value
        End Set
    End Property

    Property LEDATE_ As String
        Get
            Return LEDATE
        End Get
        Set(value As String)
            LEDATE = value
        End Set
    End Property
    Property APPDATE_ As String
        Get
            Return APPDATE
        End Get
        Set(value As String)
            APPDATE = value
        End Set
    End Property

    Property STARTDATE_ As String
        Get
            Return STDATE
        End Get
        Set(value As String)
            STDATE = value
        End Set
    End Property
    Property PROTYPE_ As Integer
        Get
            Return PROTYPE
        End Get
        Set(value As Integer)
            PROTYPE = value
        End Set
    End Property
    Property PROVIDER_ As String
        Get
            Return PROVIDER
        End Get
        Set(value As String)
            PROVIDER = value
        End Set
    End Property
    Property REGISTERNO_ As String
        Get
            Return REGISTERNO
        End Get
        Set(value As String)
            REGISTERNO = value
        End Set
    End Property
    Property COUNCIL_ As String
        Get
            Return COUNCIL
        End Get
        Set(value As String)
            COUNCIL = value
        End Set
    End Property
    Property CID_ As String
        Get
            Return CID
        End Get
        Set(value As String)
            CID = value
        End Set
    End Property
    Property PRENAME_ As String
        Get
            Return PRENAME
        End Get
        Set(value As String)
            PRENAME = value
        End Set
    End Property
    Property NAME_ As String
        Get
            Return NAME
        End Get
        Set(value As String)
            NAME = value
        End Set
    End Property
    Property LNAME_ As String
        Get
            Return LNAME
        End Get
        Set(value As String)
            LNAME = value
        End Set
    End Property

    Property EMPID_ As String
        Get
            Return EMPID
        End Get
        Set(value As String)
            EMPID = value
        End Set
    End Property

    Property NNAME_ As String
        Get
            Return NNAME
        End Get
        Set(value As String)
            NNAME = value
        End Set
    End Property

    Property NAME_EN_ As String
        Get
            Return NAME_EN
        End Get
        Set(value As String)
            NAME_EN = value
        End Set
    End Property
    Property SEX_ As String
        Get
            Return SEX
        End Get
        Set(value As String)
            SEX = value
        End Set
    End Property

    Property CLINIC_ As String
        Get
            Return CLINIC
        End Get
        Set(value As String)
            CLINIC = value
        End Set
    End Property

    Property DCL_ As String
        Get
            Return DCL
        End Get
        Set(value As String)
            DCL = value
        End Set
    End Property

    Property BDATE_ As String
        Get
            Return BDATE
        End Get
        Set(value As String)
            BDATE = value
        End Set
    End Property
    Property providerstatus_ As Boolean
        Get
            Return providerstatus
        End Get
        Set(value As Boolean)
            If value = True Then
                providerstatus = 1
            Else
                providerstatus = 0
            End If
        End Set
    End Property

    Property USREQ_ As String
        Get
            Return USREQ
        End Get
        Set(value As String)
            USREQ = value
        End Set
    End Property

    Property USMK_ As String
        Get
            Return USMK
        End Get
        Set(value As String)
            USMK = value
        End Set
    End Property
    Property selectProviderInfo_ As String
        Get
            Return selectProviderInfo
        End Get
        Set(value As String)

        End Set
    End Property

    Public Sub loadcombobox(ByVal PRENAMECmb As ComboBox)
        Dim PRENAME As DataTable
        PRENAME = connect.GetTable("SELECT `prename`, `ftprename` FROM masprename WHERE `status` = 1 ")
        Try
            With PRENAMECmb
                .DataSource = PRENAME
                .DisplayMember = "ftprename"
                .ValueMember = "prename"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Public Sub loadcombobox1(ByVal PROTYPECmb As ComboBox)
        Dim PROTYPE As DataTable
        PROTYPE = connect.GetTable("SELECT `protype`, `proname` FROM masprotype WHERE `status` = 1 ")
        Try
            With PROTYPECmb
                .DataSource = PROTYPE
                .DisplayMember = "PRONAME"
                .ValueMember = "PROTYPE"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    'Public Function SEXCombobox() As DataTable
    '    connectlocal = conlocal.NewConnection()
    '    Return connectlocal.GetTable("SELECT SEX, SEXDESC FROM MASSEX WHERE [STATUS] = 1")
    'End Function

    Public Function insertproviderData() As Boolean
        If PRENAME <> "" And NAME <> "" Then
            If TypeOf PRENAME Is String Then
                If connect.GetTable("SELECT `registerno` FROM hospemp WHERE `registerno` = '" & REGISTERNO & "'").Rows.Count > 0 Then
                    MsgBox("มีข้อมูลนี้อยู่ในระบบแล้ว")
                Else
                    Try
                        connect = CONDB.NewConnection()
                        connect.BeginTrans()
                        'connectlocal = conlocal.NewConnection()
                        Dim sql As String
                        sql = "INSERT INTO hospemp (`cid`,`registerno`,`provider`,`prename`,`name`,`lname`, `nname`, `name_en`,`sex`, `clinic`, `dcl` , `protype` , `bdate`,`stdate`,`appdate`, `ledate`, `f_cash`, `f_dr` , `f_login`, `usreq`, `status`, `d_update`, `usmk`) VALUES ("

                        sql += "'" & CID & "', "
                        sql += "'" & REGISTERNO & "', "
                        sql += "'" & PROVIDER & "', "
                        sql += "'" & PRENAME & "', "
                        sql += "'" & NAME & "', "
                        sql += "'" & LNAME & "', "
                        sql += "'" & NNAME & "', "
                        sql += "'" & NAME_EN & "', "

                        If SEX <> 0 Then
                            sql += "'" & SEX & "', "
                        Else
                            sql += "null, "
                        End If

                        If CLINIC <> 0 Then
                            sql += "'" & CLINIC & "', "
                        Else
                            sql += "null, "
                        End If

                        If DCL <> 0 Then
                            sql += "'" & DCL & "', "
                        Else
                            sql += "null, "
                        End If

                        If PROTYPE <> 0 Then
                            sql += "'" & PROTYPE & "', "
                        Else
                            sql += "null, "
                        End If

                        sql += "'" & BDATE & "', "
                        sql += "'" & STDATE & "', "
                        sql += "'" & APPDATE & "', "
                        sql += "'" & LEDATE & "', "
                        sql += "'" & F_CASH & "', "
                        sql += "'" & F_DR & "', "
                        sql += "'" & F_LOGIN & "', "
                        sql += "'" & USREQ & "', "
                        sql += "'" & providerstatus & "', "
                        sql += "CURRENT_TIMESTAMP(), "
                        sql += "'" & USMK & "'); "

                        If F_DR = 1 Then
                            sql += "SELECT LAST_INSERT_ID();"

                            Dim empid As String = connect.ExecuteScalar(sql)

                            Dim sql2 As String = ""
                            If F_DR = 1 Then
                                For i As Integer = 1 To 7
                                    sql2 += "INSERT INTO drontime (`provider`,`onday`) VALUES ('" & empid & "','" & i & "') ;"
                                Next
                            End If
                            connect.ExecuteNonQuery(sql2)
                        Else
                            connect.ExecuteNonQuery(sql)
                        End If

                        connect.CommitTrans()
                        MsgBox("ท่านได้ทำการเพิ่มข้อมูลเรียบร้อยแล้ว")
                        Return True
                    Catch ex As Exception
                        connect.RollbackTrans()
                        MsgBox(ex.ToString)
                        '& vbCrLf & ex.Message
                        Return False
                    Finally
                        connect.Dispose()
                    End Try

                End If
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Function
    Public Function editproviderData() As Boolean
        If PROVIDER > 0 And PRENAME <> "" And NAME <> "" Then
            If TypeOf NAME Is String Then
                Try
                    connect = CONDB.NewConnection()
                    connect.BeginTrans()
                    'connectlocal = conlocal.NewConnection()
                    Dim sql As String
                    sql = "UPDATE hospemp SET "
                    sql += "`cid` = '" & CID & "', "
                    sql += "`registerno` = '" & REGISTERNO & "', "
                    sql += "`provider` = '" & PROVIDER & "', "
                    sql += "`prename` = '" & PRENAME & "', "
                    sql += "`name` = '" & NAME & "', "
                    sql += "`lname` = '" & LNAME & "', "
                    sql += "`nname` = '" & NNAME & "', "
                    sql += "`name_en` = '" & NAME_EN & "', "

                    If SEX <> 0 Then
                        sql += "`sex` = '" & SEX & "', "
                    Else
                        sql += "`sex` = null, "
                    End If

                    If CLINIC <> 0 Then
                        sql += "`clinic` = '" & CLINIC & "', "
                    Else
                        sql += "`clinic` = null, "
                    End If

                    If DCL <> 0 Then
                        sql += "`dcl` = '" & DCL & "', "
                    Else
                        sql += "`dcl` = null, "
                    End If

                    If PROTYPE <> 0 Then
                        sql += "`protype` = '" & PROTYPE & "', "
                    Else
                        sql += "`protype` = null, "
                    End If

                    sql += "`bdate` = '" & BDATE & "', "
                    sql += "`stdate` = '" & STDATE & "', "
                    sql += "`appdate` = '" & APPDATE & "', "
                    sql += "`ledate` = '" & LEDATE & "', "
                    sql += "`f_cash` = '" & F_CASH & "', "
                    sql += "`f_dr` = '" & F_DR & "', "
                    sql += "`f_login` = '" & F_LOGIN & "', "
                    sql += "`f_dr` = '" & F_DR & "', "
                    sql += "`usreq` = '" & USREQ & "', "
                    sql += "`status` = '" & providerstatus & "', "
                    sql += "`d_update` = CURRENT_TIMESTAMP(), "
                    sql += "`usmk` = '" & USMK & "' "
                    sql += "WHERE `empid` = '" & EMPID & "'"

                    'connectlocal.ExecuteNonQuery("UPDATE MASPENNAME SET PENNAME = '" & PENNAME & "',PENLNAME = '" & PENLNAME & "' , SEX = '" & SEX & "', STATUS = '" & pennameStatus & "' WHERE PRENAME = '" & pennameID & "'")
                    connect.ExecuteNonQuery(sql)
                    connect.CommitTrans()

                    MsgBox("ท่านได้ทำการแก้ไขข้อมูลเรียบร้อยแล้ว")
                    Return True
                Catch ex As Exception
                    connect.RollbackTrans()
                    MsgBox(ex.Message & vbCrLf & ex.ToString)
                    Return False
                Finally
                    connect.Dispose()
                End Try
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Function
End Class
